package modle.DAO;

import modle.Bean.ApplyCarForm;
import modle.Bean.ApplyCarRecord;
import modle.Bean.Employee;
import modle.Bean.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import static modle.Bean.InfoUtil.calculateAge;

public class ApplyCarDao {

    public ApplyCarDao() {
    }
    //根据用户名来初始化申请车辆的员工信息
    public Employee getEmployeeInfo(Connection conn, String username) throws Exception {

        PreparedStatement pstmt = null;
        Employee employee = null;
        try {
            pstmt = conn.prepareStatement("select * from employeeInfo where  username= ? ");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                employee = new Employee();
                employee.setUsername(rs.getString("username"));
                employee.setEmployeeID(rs.getInt("employee_id"));
                employee.setEmail(rs.getString("email"));
                employee.setBirthday(rs.getString("birthday"));
                employee.setAge(calculateAge(rs.getString("birthday")));
                employee.setAddress(rs.getString("address"));
                employee.setDept(rs.getString("deptname"));
                employee.setPhoto_url(rs.getString("photo_url"));
                employee.setTel(rs.getString("tel"));
                employee.setSex(rs.getString("sex"));
                employee.setOnboard_date(rs.getString("onboard_date"));
                employee.setName(rs.getString("real_name"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return employee;
    }

    //添加车辆申请表
    public void addApplyCar(Connection conn, ApplyCarForm applyCarForm) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("INSERT INTO apply_vehicle (applicant, car_type, num,apply_date,start_date,start_location,destination,apply_type,remarks,status) VALUES (?,?,?,?,?,?,?,?,?,?)");
            pstmt.setInt(1, applyCarForm.getApplicant().getEmployeeID());
            pstmt.setInt(2, applyCarForm.getCar_type());
            pstmt.setInt(3, applyCarForm.getPerson_num());
            pstmt.setString(4, applyCarForm.getApply_date());
            pstmt.setString(5, applyCarForm.getStart_date());
            pstmt.setString(6, applyCarForm.getStart_location());
            pstmt.setString(7, applyCarForm.getDestination());
            pstmt.setInt(8, applyCarForm.getApply_type());
            pstmt.setString(9, applyCarForm.getRemarks());
            pstmt.setInt(10, applyCarForm.getStatus());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public void deleteApplyCar(Connection conn, ApplyCarForm applyCarForm) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("DELETE FROM apply_vehicle WHERE applicant = ? AND apply_date = ? AND start_date = ? AND start_location = ? AND destination = ? ");
            //根据用车人和用车时间以及申请时间等确定唯一申请表并删除
            pstmt.setInt(1, applyCarForm.getApplicant().getEmployeeID());
            pstmt.setString(2, applyCarForm.getApply_date());
            pstmt.setString(3, applyCarForm.getStart_date());
            pstmt.setString(4, applyCarForm.getStart_location());
            pstmt.setString(5, applyCarForm.getDestination());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public List<ApplyCarRecord> getcurrentApplyCarRecords(Connection conn ,int employeeID) {
        //该方法通过获取申请人ID来返回当前申请车辆 即只获取申请状态为未结束状态 1 2 3
        PreparedStatement pstmt = null;
        List<ApplyCarRecord> applyCarRecordList = null;
        try {
            applyCarRecordList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT *  FROM apply_records WHERE  applicant = ? AND status IN (1, 2, 3)");
            pstmt.setInt(1, employeeID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ApplyCarRecord applyCarRecord = new ApplyCarRecord();
                applyCarRecord.setApply_date(rs.getString("apply_date"));
                applyCarRecord.setStart_date(rs.getString("start_date"));
                applyCarRecord.setStart_location(rs.getString("start_location"));
                applyCarRecord.setDestination(rs.getString("destination"));
                applyCarRecord.setPerson_num(rs.getInt("num"));
                applyCarRecord.setApply_type(rs.getString("type"));
                applyCarRecord.setRemarks(rs.getString("remarks"));
                applyCarRecord.setStatus(rs.getString("status_name"));
                applyCarRecord.setCar_type(rs.getString("cartype"));
                applyCarRecordList.add(applyCarRecord);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return applyCarRecordList;
    }

    public List<ApplyCarRecord> getAllApplyCarRecords(Connection conn ,int employeeID) {
        //该方法通过获取申请人ID来返回当前申请车辆 返回申请为结束状态  4 5 6
        PreparedStatement pstmt = null;
        List<ApplyCarRecord> applyCarRecordList = null;
        try {
            applyCarRecordList = new ArrayList<>();
            pstmt = conn.prepareStatement("SELECT *  FROM apply_records WHERE  applicant = ? AND status IN (4, 5, 6) ");
            pstmt.setInt(1, employeeID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ApplyCarRecord applyCarRecord = new ApplyCarRecord();
                applyCarRecord.setApply_date(rs.getString("apply_date"));
                applyCarRecord.setStart_date(rs.getString("start_date"));
                applyCarRecord.setStart_location(rs.getString("start_location"));
                applyCarRecord.setDestination(rs.getString("destination"));
                applyCarRecord.setPerson_num(rs.getInt("num"));
                applyCarRecord.setApply_type(rs.getString("type"));
                applyCarRecord.setRemarks(rs.getString("remarks"));
                applyCarRecord.setStatus(rs.getString("status_name"));
                applyCarRecord.setCar_type(rs.getString("cartype"));
                applyCarRecordList.add(applyCarRecord);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return applyCarRecordList;
    }

}
